package com.diandian.backend.third.exchange.service;

import com.alibaba.fastjson.JSON;
import com.diandian.backend.third.exchange.contants.ThirdExchangeConstant;
import com.diandian.backend.third.exchange.dto.StoreStockDTO;
import com.diandian.backend.third.exchange.param.SkuStockQueryParam;
import com.diandian.base.boot.starter.exception.DefaultException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 零方系统库存查询
 */
@Service
public class SkuStockService {

    private static final Logger logger = LoggerFactory.getLogger(SkuStockService.class);

    @Value("${business.dbs.stock.className}")
    private String className;
    @Value("${business.dbs.stock.url}")
    private String url;
    @Value("${business.dbs.stock.username}")
    private String username;
    @Value("${business.dbs.stock.password}")
    private String password;

    /**
     * 根据门店编码和商品编码查询库存数据
     * @param data
     * @return
     */
    public StoreStockDTO querySkuStock(SkuStockQueryParam data) throws Exception{

        data.check();

        String source = data.getSource();
        String productCode = data.getProductCode();
        String skuCode = data.getSkuCode();
        String storeCode = data.getStoreCode();

        // yingbb库存
        if(ThirdExchangeConstant.THIRD_FLAG_YINGBB.equals(source)){
            List<StoreStockDTO> dataList = queryLingFangStock(storeCode, productCode);
            if(dataList != null && dataList.size() > 0){
                return dataList.get(0);
            }
        }

        throw new DefaultException(1, "未查询到库存数据");
    }

    /**
     * 从原库存库获取库存数据
     *
     * @param productCode
     * @throws Exception
     */
    private List<StoreStockDTO> queryLingFangStock(String storeCode, String productCode) throws Exception {

        // 定义SQL
        String sql = "SELECT StoreCode, GoodsCode, CurQty, TranQty, LastModificationTime FROM tbInventory where GoodsCode = '" + productCode + "' AND StoreCode = '" + storeCode + "'";

        // 建立链接
        Class.forName(className);
        Connection conn = DriverManager.getConnection(url, username, password);
        PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        // 执行查询
        ResultSet rs = ps.executeQuery();

        // 组装结果对象
        List<StoreStockDTO> resultList = new ArrayList<StoreStockDTO>();
        while (rs.next()) {
            String respStoreCode = rs.getString(1);
            String respProductCode = rs.getString(2);
            Long respCurQty = rs.getLong(3);
            Long respTranQty = rs.getLong(4);
            Date respLastModificationTime = rs.getTimestamp(5);

            StoreStockDTO storeStock = new StoreStockDTO();
            storeStock.setStoreCode(respStoreCode);
            storeStock.setProductCode(respProductCode);
            storeStock.setSkuCode(respProductCode);
            Long curQty = respCurQty == null ? 0L : respCurQty;
            Long tranQty = respTranQty == null ? 0L : respTranQty;
            storeStock.setInQty(curQty + tranQty);
            storeStock.setCurQty(curQty);
            storeStock.setTranQty(tranQty);
            storeStock.setLastModificationTime(respLastModificationTime);
            resultList.add(storeStock);
        }

        // 关闭链接
        rs.close();
        ps.close();
        conn.close();

        logger.info("从库存原始库中查询到门店{}商品{}的库存数据：{}", storeCode, productCode, JSON.toJSONString(resultList));

        return resultList;
    }
}
